USE [HCBank]
GO



CREATE TABLE [dbo].[AccountHistory]
	   (
		[Id] INT NOT NULL
				 IDENTITY(1,1)
	   ,[AccountId] INT NOT NULL
	   ,[Deposit] MONEY NOT NULL
	   ,[Status] BIT NOT NULL
					 DEFAULT ( 1 )
	   )


GO


CREATE TABLE [dbo].[Accounts]
	   (
		[Id] INT NOT NULL
				 IDENTITY(1,1)
	   ,[Number] NVARCHAR(15) NOT NULL
	   ,[PIN] VARCHAR(6) NOT NULL
	   ,[CustomerId] INT NOT NULL
	   ,[Status] BIT NOT NULL
					 DEFAULT ( (1) )
	   )


GO

CREATE TABLE [dbo].[Customers]
	   (
		[Id] INT NOT NULL
				 IDENTITY(1,1)
	   ,[Name] NVARCHAR(100) NOT NULL
	   ,[DateOfBirth] DATETIME NOT NULL
	   ,[NationId] NVARCHAR(10) NOT NULL
	   ,[Address] NVARCHAR(500) NOT NULL
	   ,[Email] NVARCHAR(100) NOT NULL
	   ,[Phone] NVARCHAR(20) NULL
	   ,[Password] VARCHAR(64) NOT NULL
	   ,[Status] BIT NOT NULL
					 DEFAULT ( 1 )
	   )


GO

CREATE TABLE [dbo].[Transaction]
	   (
		[Id] INT NOT NULL
				 IDENTITY(1,1)
	   ,[FromAccount] INT NOT NULL
	   ,[ToAccount] INT NOT NULL
	   ,[Amount] MONEY NOT NULL
	   ,[TransactionTime] DATETIME NOT NULL
								   DEFAULT ( GETDATE() )
	   ,[Status] BIT NOT NULL
					 DEFAULT ( 1 )
	   )


ALTER TABLE [dbo].[AccountHistory] ADD PRIMARY KEY ([Id])
GO


ALTER TABLE [dbo].[Accounts] ADD PRIMARY KEY ([Id])
GO

ALTER TABLE [dbo].[Customers] ADD PRIMARY KEY ([Id])
GO

ALTER TABLE [dbo].[Transaction] ADD PRIMARY KEY ([Id])
GO

ALTER TABLE [dbo].[AccountHistory] ADD FOREIGN KEY ([AccountId]) REFERENCES [dbo].[Accounts] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

ALTER TABLE [dbo].[Accounts] ADD FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customers] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

ALTER TABLE [dbo].[Transaction] ADD FOREIGN KEY ([FromAccount]) REFERENCES [dbo].[Accounts] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[Transaction] ADD FOREIGN KEY ([ToAccount]) REFERENCES [dbo].[Accounts] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
